/**
 * 
 */
package userService;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import userEntity.MembershipEntity;
import userEntity.MembershipRenewalEntity;

/**
 * @author Yokesh Kumar
 *
 */
public class MembershipService {

	private Connection connection;
	private PreparedStatement statement;

	public MembershipService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	/**
	 * This method stores the membership values and updates the
	 * customer table
	 * @param membership
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet createMembership(MembershipEntity membership) throws SQLException{
		ResultSet rs = null;
		String query = "INSERT INTO membership values(null,?,current_date,current_date + interval 1 year,?,?,?,?)";
		String query2 = "Select last_insert_id() as last_id FROM membership";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, membership.getPoints());
			statement.setString(2, membership.getStatus());
			statement.setDouble(3, membership.getJoiningFee());
			statement.setInt(4,membership.getJoinPaymentId());
			statement.setString(5,membership.getPhoneNumber());
			statement.executeUpdate();

			statement = connection.prepareStatement(query2);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * @param membershipId
	 * @param cancelDesc
	 * @throws SQLException 
	 */
	public void cancelMembership(int membershipId, String cancelDesc) throws SQLException {
		String query = "Insert into membership_cancel values (?,current_date,?)";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1,membershipId);
			statement.setString(2, cancelDesc);
			statement.executeUpdate();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

	}

	public void updateMembershipStatus(int membershipId, String status) throws SQLException{
		String query = "UPDATE membership set status=? where membershipId=?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, status);
			statement.setInt(2,membershipId);
			statement.executeUpdate();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}

	/**
	 * This method inserts the membership renewal details, returns the renewal ID
	 * @param renewalEntity
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet renewMembership(MembershipRenewalEntity renewalEntity) throws SQLException{
		ResultSet rs = null;
		String query = "INSERT INTO membership_renewal values(?,?,current_date,?,?,null)";
		String query2 = "Select last_insert_id() as last_id FROM membership_renewal";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, renewalEntity.getCustphoneNumber());
			statement.setInt(2,renewalEntity.getMembershipId());
			statement.setDouble(3, renewalEntity.getRenewalAmount());
			statement.setInt(4,renewalEntity.getPaymentId());
			statement.executeUpdate();
			statement = connection.prepareStatement(query2);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

		return rs;
	}

	/**
	 * This method fetches the points history of the customer from
	 * points redeemed table
	 * @param memberShipId
	 * @Pre membershipId retrieved from MembershipLogic class
	 * @Post The selected values are passed to Membership logic
	 * @return
	 * @throws SQLException 
	 */
	//fetchMembershipPointsHistory(int memberID)
	public ResultSet fetchMembershipPointsHistory(Integer membershipId) throws SQLException{
		ResultSet rs = null;
		
		String query = 
				"select a.VehicleRentId as RentId,a.rentDate,a.returnDate, b.VehicleName, b.vehicleType, a.pointsRedeemed,a.pointsGained "+
						"from (select p.memberId,p.VehicleRentId,p.rentDate,re.returnDate ,p.pointsRedeemed,p.pointsGained "+ 
						"from (select p.memberId, p.VehicleRentId,r.rentDate,p.pointsRedeemed,p.pointsGained "+ 
						"from points_redeemed p left join rent r "+
						"on p.VehicleRentId = r.rentId) p left join return_vehicle re "+
						"on p.VehicleRentId = re.returnId) a, (select rv.rentId, v.vehicleType, v.VehicleName "+
						"from rental_vehicle rv left join vehicle v "+
						"on rv.vehicleId = v.vehicleId) b "+
						"where a.VehicleRentId=b.rentId and a.memberId = ? "; //and a.memberId = ?
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, membershipId);
			rs =  statement.executeQuery();
			System.out.println("Inside Service Class \n Membership ID retrieved from Service is "+membershipId);
			
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			e1.printStackTrace();
		}

		return rs;

	}


	/**
	 * @throws SQLException 
	 * 
	 */
	public void updateCustomerIsMember(String phNo, boolean isMember) throws SQLException {
		String query = "UPDATE customer set isMember=? where phoneNumber=?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setBoolean(1, isMember);
			statement.setString(2, phNo);
			statement.executeUpdate();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}
	
public void updatePointsRedeemed(int memberId,int rentId,int pointsRedeemed,int pointsGained) throws SQLException{
	System.out.println("update points redeemed table");
	String query = "INSERT into superrentdb.points_redeemed VALUES (?,?,?,?);";
	try {
		connection.setAutoCommit(false);
		statement = connection.prepareStatement(query);
		statement.setInt(1, memberId);
		statement.setInt(2, rentId);
		statement.setInt(3, pointsRedeemed);
		statement.setInt(4, pointsGained);
		statement.executeUpdate();
	} 
	catch (SQLException e1) {
		if(connection != null){
			System.out.println("transaction is rolled back");
			try {
				connection.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		e1.printStackTrace();
		throw e1;
		}

}

	public void updateMemberPoints(int points,int membershipId) throws SQLException{
		System.out.println("update mem points");
		String query = "UPDATE membership SET points=? WHERE membershipId=?;" ;
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, points);
			statement.setInt(2, membershipId);
			statement.executeUpdate();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

	}

	/**
	 * @param format
	 * @throws SQLException 
	 */
	public void updateExpiryDate(Date expDate,int membershipId) throws SQLException {
		String query = "UPDATE membership set expiryDate=?,status=? where membershipId=?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setDate(1, expDate);
			statement.setString(2, Constants.MEMBERSHIP_ACTIVE);
			statement.setInt(3, membershipId);
			statement.executeUpdate();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}

	public void transactionCommit() throws SQLException{
		if(connection!=null){
			try {
				connection.commit();
			} catch (SQLException e) {
				if(connection != null){
					System.out.println("transaction is rolled back");
					try {
						connection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
						throw e1;
					}
				}
				e.printStackTrace();
				throw e;
			}
			finally{
				try {
					connection.setAutoCommit(true);
				}
				catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
	}

	public void transactionRollBack() throws SQLException{

		try {
			if(connection != null){
				connection.rollback();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}

		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
	}

	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}
	/**
	 * @param phNo
	 * @throws SQLException 
	 */
	public ResultSet fetchEmailID(String phNo) throws SQLException {
		String query = "Select emailId from user where phoneNumber = ?";
		ResultSet rs = null;
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1,phNo);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}




}
